﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using CRMFramework;

namespace CRMBiz.Contact
{
    public static class VehicleHandler
    {
        /// <summary>
        /// function to get Vehicle info list
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns>VehicleData</returns>
        public static DataSet SelectVehicle(int pageIndex, int pageSize, int CurrentContact_id)
        {
            DataSet ds;
            try
            {
                var sbSql = new StringBuilder("SELECT *,DATE_FORMAT(ExpiryDate,'%d-%b-%Y') as ExpDate,(select count(*) from crm_vehicle where CurrentContact_id=" + CurrentContact_id + ") as Total from crm_vehicle where CurrentContact_id=" + CurrentContact_id + " order by Brand  desc ");
                sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDataset(sbSql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        /// <summary>
        /// function to update Vehicle info
        /// </summary>
        ///<param name="VehicleID"></param>
        /// <returns>VehicleData</returns>
        public static int EditVehicleInfo(int VehicleID, int CurrentContact_id, string Brand, string Model, string Owner, string Finance, string VehicleYear, string Insurance, string VIN, string EngineNo, string RegNo, string Colour, string Badge, string Make, string VehicleValue, string VehicleOwing, string VehicleLender, string VehiclePayment, string ValueSource, string OwningSource, string SecurityStatus, string ExpiryDate, string VehicleComment)
        {
            int intReturnValue = 0;
            try
            {
                DateTime p_ExpiryDate = new DateTime();
                DateTimeFormatInfo p_date = new DateTimeFormatInfo();
                p_date.ShortDatePattern = "yyyy-MM-dd";
                p_date.DateSeparator = "-";

                if (!string.IsNullOrEmpty(ExpiryDate))
                    p_ExpiryDate = Convert.ToDateTime(ExpiryDate, p_date);

                var para = new[] {
                     DBhelper.SetParameter("@VehicleID", VehicleID),
                    DBhelper.SetParameter("@CurrentContact_id", CurrentContact_id),
                    DBhelper.SetParameter("@Brand", Brand),
                    DBhelper.SetParameter("@Model", Model),
                    DBhelper.SetParameter("@Owner", Owner),
                    DBhelper.SetParameter("@Finance", Finance),
                    DBhelper.SetParameter("@VehicleYear", VehicleYear),
                    DBhelper.SetParameter("@Insurance", Insurance),
                    DBhelper.SetParameter("@VIN",VIN),
                    DBhelper.SetParameter("@EngineNo",EngineNo),
                    DBhelper.SetParameter("@RegNo",RegNo),
                    DBhelper.SetParameter("@Colour",Colour),
                    DBhelper.SetParameter("@Badge",Badge),
                    DBhelper.SetParameter("@Make",Make),
                    DBhelper.SetParameter("@VehicleValue",VehicleValue),
                    DBhelper.SetParameter("@VehicleOwing",VehicleOwing),   
                    DBhelper.SetParameter("@VehicleLender",VehicleLender),
                    DBhelper.SetParameter("@VehiclePayment",VehiclePayment),   
                    DBhelper.SetParameter("@ValueSource",ValueSource),
                    DBhelper.SetParameter("@OwningSource",OwningSource),   
                    DBhelper.SetParameter("@SecurityStatus",SecurityStatus),
                    DBhelper.SetParameter("@ExpiryDate",p_ExpiryDate),   
                    DBhelper.SetParameter("@VehicleComment",VehicleComment),   
                };

                string sbSql = "update crm_vehicle set Brand =@Brand,Model=@Model,Owner=@Owner,Owner=@Owner,Finance=@Finance,VehicleYear=@VehicleYear,Insurance=@Insurance,VIN=@VIN,EngineNo=@EngineNo,RegNo=@RegNo,Colour=@Colour,Badge=@Badge,Make=@Make,VehicleValue=@VehicleValue,VehicleOwing=@VehicleOwing,VehicleLender=@VehicleLender,VehiclePayment=@VehiclePayment,ValueSource=@ValueSource,OwningSource=@OwningSource,SecurityStatus=@SecurityStatus,ExpiryDate=@ExpiryDate,VehicleComment=@VehicleComment where VehicleID = @VehicleID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        /// <summary>
        /// function to add new Vehicle info
        /// </summary>
        /// <param name="Contact_ID"></param>
        /// <returns>dataset</returns>
        public static int AddVehicleInfo(string Brand, string Model, string Owner, string Finance, string VehicleYear, string Insurance, string VIN, string EngineNo, string RegNo, string Colour, string Badge, string Make, string VehicleValue, string VehicleOwing, string VehicleLender, string VehiclePayment, string ValueSource, string OwningSource, string SecurityStatus, string ExpiryDate, string VehicleComment, int CurrentContact_id)
        {
            int intReturnValue = 0;
            try
            {
                DateTime p_ExpiryDate = new DateTime();
                DateTimeFormatInfo p_date = new DateTimeFormatInfo();
                p_date.ShortDatePattern = "yyyy-MM-dd";
                p_date.DateSeparator = "-";

                if (!string.IsNullOrEmpty(ExpiryDate))
                    p_ExpiryDate = Convert.ToDateTime(ExpiryDate, p_date);

                var para = new[] {
                    DBhelper.SetParameter("@Brand", Brand),
                    DBhelper.SetParameter("@Model", Model),
                    DBhelper.SetParameter("@Owner", Owner),
                    DBhelper.SetParameter("@Finance", Finance),
                    DBhelper.SetParameter("@VehicleYear", VehicleYear),
                    DBhelper.SetParameter("@Insurance", Insurance),
                    DBhelper.SetParameter("@VIN",VIN),
                    DBhelper.SetParameter("@EngineNo",EngineNo),
                    DBhelper.SetParameter("@RegNo",RegNo),
                    DBhelper.SetParameter("@Colour",Colour),
                    DBhelper.SetParameter("@Badge",Badge),
                    DBhelper.SetParameter("@Make",Make),
                    DBhelper.SetParameter("@VehicleValue",VehicleValue),
                    DBhelper.SetParameter("@VehicleOwing",VehicleOwing),   
                    DBhelper.SetParameter("@VehicleLender",VehicleLender),
                    DBhelper.SetParameter("@VehiclePayment",VehiclePayment),   
                    DBhelper.SetParameter("@ValueSource",ValueSource),
                    DBhelper.SetParameter("@OwningSource",OwningSource),   
                    DBhelper.SetParameter("@SecurityStatus",SecurityStatus),
                    DBhelper.SetParameter("@ExpiryDate",p_ExpiryDate),   
                    DBhelper.SetParameter("@VehicleComment",VehicleComment),   
                    DBhelper.SetParameter("@CurrentContact_id", CurrentContact_id),
                };

                string sbSql = "insert into crm_vehicle(Brand,Model,Owner,Finance,VehicleYear,Insurance,VIN,EngineNo,RegNo,Colour,Badge,Make,VehicleValue,VehicleOwing,VehicleLender,VehiclePayment,ValueSource,OwningSource,SecurityStatus,ExpiryDate,VehicleComment,CurrentContact_id) values(@Brand,@Model,@Owner,@Finance,@VehicleYear,@Insurance,@VIN,@EngineNo,@RegNo,@Colour,@Badge,@Make,@VehicleValue,@VehicleOwing,@VehicleLender,@VehiclePayment,@ValueSource,@OwningSource,@SecurityStatus,@ExpiryDate,@VehicleComment,@CurrentContact_id)";
                DBhelper.ExecuteNonQuery(sbSql, para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        /// <summary>
        /// function to delete Vehicle info
        /// </summary>
        /// <param name="VehicleID"></param>
        /// <returns>dataset</returns>
        public static bool DeleteVehicleInfo(int VehicleID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@VehicleID", VehicleID),
                };

                DBhelper.ExecuteNonQuery("delete from crm_vehicle where VehicleID=@VehicleID", para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
